package com.kb.mapper.sqlServer;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.kb.domain.BomRfq;
import com.kb.domain.BomRfqDetail;
import com.kb.domain.RepurchaseOrder;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface CommonMapper extends BaseMapper<RepurchaseOrder> {
    @Select({
            "<script>",
            "SELECT TOP 10 *,",
            "       (SELECT TOP 1 SGPrice FROM bom_Rfq WHERE CustomerNo = t.CustomerId AND ProductNo = t.ProjectNo) AS SGPrice,",
            "       CONCAT(BomNO, '_V', REPLACE(REPLACE(VersionNo, '.', ''), 'V', '')) AS VersionID",
            "FROM (",
            "    SELECT *,",
            "           ROW_NUMBER() OVER (PARTITION BY [ProNo] ORDER BY [CreateDate] DESC) AS Order_Idx",
            "    FROM bom_BomR",
            "    WHERE [Part_No] = #{Part_No}",
            ") t",
            "WHERE Order_Idx = 1",
            "  AND status = 2",
            "  AND valid = 1",
            "  AND CustomerId = 'E0005'",
            "</script>"
    })
    List<RepurchaseOrder> getRepurchaseOrder(@Param("Part_No") String Part_No);

    @Select({
            "<script>",
            "SELECT TOP 500 * FROM bom_RfqDetail",
            "WHERE RfqId IN (",
            "    SELECT Id FROM bom_Rfq WHERE RfqNo = #{Rfq_No}",
            ")",
            "</script>"
    })

    List<BomRfqDetail> getBomDetail(@Param("Rfq_No")String Rfq_No);

    @Select({
            "<script>",
            "SELECT QuetoBOM FROM [dbo].[bom_Rfq] WHERE RfqNo = #{rfqNo}",
            "</script>"
    })
    String getBomFile(@Param("rfqNo") String rfqNo);

    @Select({
            "<script>",
            "SELECT d.* FROM bom_Rfq f LEFT JOIN bom_RfqDetail d on f.id = d.RfqId WHERE f.RfqNo = #{rfqNo}",
            "</script>"
    })
    List<BomRfqDetail> getBomContent(String rfqNo);

    @Select({
            "<script>",
            "SELECT * FROM bom_Rfq f WHERE f.RfqNo = #{rfqNo}",
            "</script>"
    })
    BomRfq getBom(String rfqNo);
}
